
use chrono::NaiveDate;
use clickhouse_rs::types::SqlType;


use super::{get_ch_pool, get_config};

pub async fn table_is_exist(table_name:&str) -> anyhow::Result<bool> {
    let mut client = get_ch_pool().get_handle().await?;
    let db_name = get_config().clickhouse.database.as_str();
    let sql_str = format!(
        "select count(*) from system.tables where database='{}' and name='{}'",
        db_name,
        table_name
    );
    let block = client.query(sql_str).fetch_all().await?;
    let mut count = 0u64;
    if let Some(row) = block.rows().next() {
        count = row.get(0)?
    }
    Ok(count > 0)
}
pub async fn table_has_data_with_date(table_name:&str,date:&NaiveDate) -> anyhow::Result<bool> {
    let mut client = get_ch_pool().get_handle().await?;
    let db_name = get_config().clickhouse.database.as_str();
    let sql_str=format!("select top 1 sorting_key from system.tables where database='{db_name}' and name='{table_name}'");
    let block = client.query(sql_str).fetch_all().await?;
    let sorting_key:String;
    if let Some(row) = block.rows().next() {
        sorting_key = row.get(0)?
    }else{
        return Err(anyhow::anyhow!("表{table_name}在数据库{db_name}中不存在"));
    }
    let keys=sorting_key.split(',').collect::<Vec<&str>>();
    if keys[0].is_empty() {
        return Err(anyhow::anyhow!("表{table_name}中没有日期字段"));
    }
    let sql_str = format!(
        "select count(*) from {} where {}='{}'",
        table_name,
        keys[0],
        date.format("%Y-%m-%d").to_string()
    );
    let block = client.query(sql_str).fetch_all().await?;
    let mut count = 0u64;
    if let Some(row) = block.rows().next() {
        count = row.get(0)?
    }
    Ok(count > 0)
}

pub async fn get_columns(table_name: &str) -> anyhow::Result<Vec<(String, SqlType)>> {
    let mut client = get_ch_pool().get_handle().await?;
    let sql_str = format!("select top 1 * from {}", table_name);
    let block = client.query(sql_str).fetch_all().await?;
    let cols: &[clickhouse_rs::types::Column<clickhouse_rs::types::Complex>] = block.columns();
    let cols: Vec<(String, SqlType)> = cols
        .iter()
        .map(|col| (col.name().to_string(), col.sql_type().clone()))
        .collect();
    Ok(cols)
}

pub async fn get_dict_tables() -> anyhow::Result<Vec<(String, String)>> {
    let db_name=get_config().clickhouse.database.as_str();
    let asse_dicts=&get_config().asse_dicts;
    let and_where=if asse_dicts.len() > 0 {
        let mut aw=" and name not in (".to_owned();
        for ad in asse_dicts.iter() {
            aw.push_str("'");
            aw.push_str(ad.as_str());
            aw.push_str("'");
            aw.push(',');
        }
        aw.pop();
        aw.push(')');
        aw
    }else{
        "".to_string()
    };
	let sql= format!("select name,loading_dependencies_table[1] dep_table from system.tables where database ='{db_name}' and engine='Dictionary' and loading_dependencies_database[1]='{db_name}' {and_where}");
    let mut clinet= get_ch_pool().get_handle().await?;
    let block=clinet.query(sql).fetch_all().await?;
    let mut res:Vec<(String, String)>=Vec::new();
    for row in block.rows() {
        let name:String=row.get(0)?;
        let dep_table:String=row.get(1)?;
        res.push((name, dep_table));
    }
    Ok(res)
}

//创建临时表
pub async fn create_tmp_table(table_name: &str, tmp_table_name: &str) -> anyhow::Result<()> {
	let sql=format!("create table if not exists {} AS {}",tmp_table_name,table_name);
    let mut clinet= get_ch_pool().get_handle().await?;
    clinet.execute(sql).await?;
	Ok(())
}

pub async fn delete_all_tmp_table() -> anyhow::Result<()> {
	let sql= format!("select name from system.tables where database ='{}' AND (name like 'tmp_%' or name like 'TMP_%')",get_config().clickhouse.database);
    let mut clinet= get_ch_pool().get_handle().await?;
    let block=clinet.query(sql).fetch_all().await?;
    for row in block.rows() {
        let tname:String=row.get(0)?;
        clinet.execute(format!("drop table {tname}")).await?;
    }
    Ok(())
}
///加载汇率表
pub async fn load_feqp(date: &NaiveDate) -> anyhow::Result<u64> {
    let sql= "CREATE TABLE IF NOT EXISTS JOIN_FEQP_CURR (UKEY UInt8,BGDT Date,MIDV Float64,AMVL Float64) ENGINE = Join(ANY,LEFT,UKEY)";
    let mut client= get_ch_pool().get_handle().await?;
    client.execute(sql).await?;
    let sql= "TRUNCATE TABLE JOIN_FEQP_CURR";
    client.execute(sql).await?;
    let date= date.format("%Y-%m-%d").to_string();
    let sql= format!("INSERT INTO JOIN_FEQP_CURR SELECT CYNO,BGDT,MIDV,AMVL FROM T_BASE_FEQP WHERE BGDT = toDate('{date}')");
    client.execute(sql).await?;
    let block=client.query("select count() from JOIN_FEQP_CURR").fetch_all().await?;
    let row=block.rows().next().unwrap();
    let count:u64=row.get(0)?;
    Ok(count)
}
pub async fn reload_dicts(dicts: &Vec<String>)-> anyhow::Result<()> {
    let mut client= get_ch_pool().get_handle().await?;
    for dict in dicts.iter() {
        let sql = format!("SYSTEM RELOAD DICTIONARY {dict}");
        client.execute(sql).await?;
    }
    Ok(())
}

